package com.chixing.ih.module.programmer.mapper.provider;

import com.chixing.ih.module.programmer.pojo.Programmer;
import com.chixing.ih.module.programmer.pojo.dto.ProgrammerDTO;
import com.chixing.ih.status.IsDeleteEnum;
import com.chixing.ih.util.EmptyUtil;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 刘岩
 * Date: 2020-09-24
 * Time: 20:02
 *
 * @version 1.0
 */

public class ProgrammerMapperProvider {


    public String updateOne(Programmer programmer) {
        return new SQL() {{
            UPDATE("tbl_user");
            if (!EmptyUtil.isEmpty(programmer.getProgrammerEmail())) {
                SET("user_Email=#{programmerEmail}");
            }
            if (!EmptyUtil.isEmpty(programmer.getProgrammerTelephone())) {
                SET("user_telephone=#{programmerTelephone}");
            }
            if (!EmptyUtil.isEmpty(programmer.getAlipayAccount())) {
                SET("alipay_account=#{alipayAccount}");
            }
            if (!EmptyUtil.isEmpty(programmer.getWechatAccount())) {
                SET("wechat_account=#{wechatAccount}");
            }
            if (!EmptyUtil.isEmpty(programmer.getQqAccount())) {
                SET("qq_account=#{qqAccount}");
            }
            WHERE("user_id=#{programmerId}");

        }}.toString();
    }

    public String updateTime() {
        return new SQL() {{
            UPDATE("tbl_user");
            SET("update_time=#{updateTime}");
            WHERE("user_id=#{userId}");

        }}.toString();
    }

    public String updatePassword(ProgrammerDTO programmerDTO) {
        return new SQL() {{
            UPDATE("tbl_user");
            if (!EmptyUtil.isEmpty(programmerDTO.getNewPassword())) {
                SET("user_password=#{newPassword}");
            }
            WHERE("user_id=#{programmerId}");

        }}.toString();
    }

    public String updatePerIntroduce(Programmer programmer) {
        return new SQL() {{
            UPDATE("tbl_user");
            if (!EmptyUtil.isEmpty(programmer.getProgrammerIntroduction())) {
                SET("user_introduction=#{programmerIntroduction}");
            }
            WHERE("user_id=#{programmerId}");

        }}.toString();
    }


    public String deleteOneById() {
        return null;
    }


    public String getOneById() {
        return new SQL() {{
            SELECT("*");
            FROM("tbl_user");
            WHERE("user_id=#{programmerId}");
        }}.toString();

    }

    public String getUserIds() {
        return new SQL() {{
            SELECT("user_id");
            FROM("tbl_user");
        }}.toString();

    }

    public String getPerIntroduce() {
        return new SQL() {{
            SELECT("user_introduction");
            FROM("tbl_user");
            WHERE("user_id=#{programmerId}");
        }}.toString();
    }


    //    public String listAll() {
//        return new SQL() {{
//            SELECT("any_value(u.user_id) user_id,any_value(u.nick_name)nick_name,any_value(i.image_url) image_url,any_value(u.user_company) user_company,any_value(u.user_career) user_career,any_value(w.salary) salary,any_value(w.region_city) region_city,any_value(w.work_way)work_way,any_value(w.working_day_begin_time) working_day_begin_time ,any_value(w.working_day_end_time) working_day_end_time,any_value(w.weekend_working_begin_time) weekend_working_begin_time,any_value(w.weekend_working_end_time) weekend_working_end_time,any_value(truncate(sum(attitude_score+punctuality_score+skill_score)/count(1)/3,1)) star_rating");
//            FROM("tbl_user u LEFT JOIN tbl_work w on u.user_id=w.user_id LEFT JOIN tbl_user_image i ON u.user_id=i.user_id  LEFT JOIN tbl_evaluate e on w.user_id=e.user_evaluated_id");
//            GROUP_BY("u.user_id");
//        }}.toString();
//
//    }


    public String listAllNum() {
        return new SQL() {{
            SELECT("count(1)");
            FROM("tbl_user");
            WHERE("is_deleted=0");
        }}.toString();

    }

    public String getOneApply() {
        return new SQL() {{
            SELECT("any_value(u.user_id) user_id,any_value(u.user_email) user_email,any_value(u.nick_name)nick_name,any_value(i.image_url) image_url,any_value(u.user_company) user_company,any_value(u.user_career) user_career,any_value(w.salary) salary,any_value(w.region_city) region_city,any_value(w.work_way)work_way,any_value(w.working_day_begin_time) working_day_begin_time ,any_value(w.working_day_end_time) working_day_end_time,any_value(w.weekend_working_begin_time) weekend_working_begin_time,any_value(w.weekend_working_end_time) weekend_working_end_time,any_value(truncate(sum(attitude_score+punctuality_score+skill_score)/count(1)/3,1)) star_rating");
            FROM("tbl_user u LEFT JOIN tbl_work w on u.user_id=w.user_id LEFT JOIN tbl_user_image i ON u.user_id=i.user_id  LEFT JOIN tbl_evaluate e on w.user_id=e.user_evaluated_id");
            WHERE("u.user_id=#{userId}");
            GROUP_BY("u.user_id");
        }}.toString();

    }

    public String updateInfo(Programmer programmer) {
        return new SQL() {{
            UPDATE("tbl_user");
            if (!EmptyUtil.isEmpty(programmer.getNickName())) {
                SET("nick_name=#{nickName}");
            }
            if (!EmptyUtil.isEmpty(programmer.getProgrammerStatus())) {
                SET("user_status=#{programmerStatus}");
            }
            if (!EmptyUtil.isEmpty(programmer.getProgrammerCareer())) {
                SET("user_career=#{programmerCareer}");
            }
            if (!EmptyUtil.isEmpty(programmer.getProgrammerCompany())) {
                SET("user_company=#{programmerCompany}");
            }
            if (!EmptyUtil.isEmpty(programmer.getProgrammerEmail())) {
                SET("user_email=#{programmerEmail}");
            }
            if (!EmptyUtil.isEmpty(programmer.getProgrammerPosition())) {
                SET("user_position=#{programmerPosition}");
            }
            WHERE("user_id=#{programmerId}");

        }}.toString();


    }
//    SELECT any_value(u.user_id) user_id,any_value(u.nick_name)nick_name,any_value(i.image_url) image_url,any_value(u.user_company) user_company,any_value(u.user_career) user_career,any_value(w.salary) salary,any_value(w.region_city) region_city,any_value(w.work_way)work_way,any_value(w.working_day_begin_time) working_day_begin_time ,any_value(w.working_day_end_time) working_day_end_time,any_value(w.weekend_working_begin_time) weekend_working_begin_time,any_value(w.weekend_working_end_time) weekend_working_end_time,any_value(truncate(sum(attitude_score+punctuality_score+skill_score)/count(1)/3,1)) star_rating
//    FROM tbl_user u LEFT JOIN
//    tbl_work w on u.user_id=w.user_id LEFT JOIN
//    tbl_user_image i on u.user_id=i.user_id
//    LEFT JOIN
//    tbl_evaluate e on u.user_id=e.user_evaluated_id
//    GROUP BY u.user_id
//    ORDER BY salary asc

    public String findAllByCondition(String programmerCareer, String workPlace, Integer workWay,
                                     List<Integer> list) {
        return new SQL() {{
            if (!EmptyUtil.isEmpty(list)) {
                if (list.size() == 1) {
                    if (list.get(0) == 1) {
                        SELECT("any_value(u.user_id) user_id,any_value(u.nick_name)nick_name,any_value(i.image_url) image_url,any_value(u.user_company) user_company,any_value(u.user_career) user_career,any_value(w.salary) salary,any_value(w.region_city) region_city,any_value(w.work_way)work_way,any_value(w.working_day_begin_time) working_day_begin_time ,any_value(w.working_day_end_time) working_day_end_time,any_value(w.weekend_working_begin_time) weekend_working_begin_time,any_value(w.weekend_working_end_time) weekend_working_end_time,any_value(truncate(sum(attitude_score+punctuality_score+skill_score)/count(1)/3,1)) star_rating");
                        FROM("tbl_user u LEFT JOIN tbl_work w on u.user_id=w.user_id LEFT JOIN tbl_user_image i ON u.user_id=i.user_id  LEFT JOIN tbl_evaluate e on w.user_id=e.user_evaluated_id");
                        if (!EmptyUtil.isEmpty(programmerCareer)) {
                            WHERE("user_career= #{programmerCareer}");
                        }
                        if (!EmptyUtil.isEmpty(workPlace)) {
                            WHERE("region_city = #{workPlace}");
                        }
                        if (!EmptyUtil.isEmpty(workWay)) {
                            WHERE("work_way = #{workWay}");
                        }
                        WHERE("u.is_deleted = " + IsDeleteEnum.ZERO.status);
                        GROUP_BY("u.user_id");
                        ORDER_BY("sum(attitude_score+punctuality_score+skill_score)/count(1)/3 asc");
                    }

                    if (list.get(0) == 2) {
                        SELECT("any_value(u.user_id) user_id,any_value(u.nick_name)nick_name,any_value(i.image_url) image_url,any_value(u.user_company) user_company,any_value(u.user_career) user_career,any_value(w.salary) salary,any_value(w.region_city) region_city,any_value(w.work_way)work_way,any_value(w.working_day_begin_time) working_day_begin_time ,any_value(w.working_day_end_time) working_day_end_time,any_value(w.weekend_working_begin_time) weekend_working_begin_time,any_value(w.weekend_working_end_time) weekend_working_end_time,any_value(truncate(sum(attitude_score+punctuality_score+skill_score)/count(1)/3,1)) star_rating");
                        FROM("tbl_user u LEFT JOIN tbl_work w on u.user_id=w.user_id LEFT JOIN tbl_user_image i ON u.user_id=i.user_id  LEFT JOIN tbl_evaluate e on w.user_id=e.user_evaluated_id");
                        if (!EmptyUtil.isEmpty(programmerCareer)) {
                            WHERE("user_career= #{programmerCareer}");
                        }
                        if (!EmptyUtil.isEmpty(workPlace)) {
                            WHERE("region_city = #{workPlace}");
                        }
                        if (!EmptyUtil.isEmpty(workWay)) {
                            WHERE("work_way = #{workWay}");
                        }
                        WHERE("u.is_deleted = " + IsDeleteEnum.ZERO.status);
                        GROUP_BY("u.user_id");
                        ORDER_BY("sum(attitude_score+punctuality_score+skill_score)/count(1)/3 desc");
                    }

                    if (list.get(0) == 3) {
                        SELECT("any_value(u.user_id) user_id,any_value(u.nick_name)nick_name,any_value(i.image_url) image_url,any_value(u.user_company) user_company,any_value(u.user_career) user_career,any_value(w.salary) salary,any_value(w.region_city) region_city,any_value(w.work_way)work_way,any_value(w.working_day_begin_time) working_day_begin_time ,any_value(w.working_day_end_time) working_day_end_time,any_value(w.weekend_working_begin_time) weekend_working_begin_time,any_value(w.weekend_working_end_time) weekend_working_end_time,any_value(truncate(sum(attitude_score+punctuality_score+skill_score)/count(1)/3,1)) star_rating");
                        FROM("tbl_user u LEFT JOIN tbl_work w on u.user_id=w.user_id LEFT JOIN tbl_user_image i ON u.user_id=i.user_id  LEFT JOIN tbl_evaluate e on w.user_id=e.user_evaluated_id");
                        if (!EmptyUtil.isEmpty(programmerCareer)) {
                            WHERE("user_career= #{programmerCareer}");
                        }
                        if (!EmptyUtil.isEmpty(workPlace)) {
                            WHERE("region_city = #{workPlace}");
                        }
                        if (!EmptyUtil.isEmpty(workWay)) {
                            WHERE("work_way = #{workWay}");
                        }
                        WHERE("u.is_deleted = " + IsDeleteEnum.ZERO.status);
                        GROUP_BY("u.user_id");
                        ORDER_BY("salary asc");
                    }

                    if (list.get(0) == 4) {
                        SELECT("any_value(u.user_id) user_id,any_value(u.nick_name)nick_name,any_value(i.image_url) image_url,any_value(u.user_company) user_company,any_value(u.user_career) user_career,any_value(w.salary) salary,any_value(w.region_city) region_city,any_value(w.work_way)work_way,any_value(w.working_day_begin_time) working_day_begin_time ,any_value(w.working_day_end_time) working_day_end_time,any_value(w.weekend_working_begin_time) weekend_working_begin_time,any_value(w.weekend_working_end_time) weekend_working_end_time,any_value(truncate(sum(attitude_score+punctuality_score+skill_score)/count(1)/3,1)) star_rating");
                        FROM("tbl_user u LEFT JOIN tbl_work w on u.user_id=w.user_id LEFT JOIN tbl_user_image i ON u.user_id=i.user_id  LEFT JOIN tbl_evaluate e on w.user_id=e.user_evaluated_id");
                        if (!EmptyUtil.isEmpty(programmerCareer)) {
                            WHERE("user_career= #{programmerCareer}");
                        }
                        if (!EmptyUtil.isEmpty(workPlace)) {
                            WHERE("region_city = #{workPlace}");
                        }
                        if (!EmptyUtil.isEmpty(workWay)) {
                            WHERE("work_way = #{workWay}");
                        }
                        WHERE("u.is_deleted = " + IsDeleteEnum.ZERO.status);
                        GROUP_BY("u.user_id");
                        ORDER_BY("salary desc");

                    }
                }
            } else {
                SELECT("any_value(u.user_id) user_id,any_value(u.nick_name)nick_name,any_value(i.image_url) image_url,any_value(u.user_company) user_company,any_value(u.user_career) user_career,any_value(w.salary) salary,any_value(w.region_city) region_city,any_value(w.work_way)work_way,any_value(w.working_day_begin_time) working_day_begin_time ,any_value(w.working_day_end_time) working_day_end_time,any_value(w.weekend_working_begin_time) weekend_working_begin_time,any_value(w.weekend_working_end_time) weekend_working_end_time,any_value(truncate(sum(attitude_score+punctuality_score+skill_score)/count(1)/3,1)) star_rating");
                FROM("tbl_user u LEFT JOIN tbl_work w on u.user_id=w.user_id LEFT JOIN tbl_user_image i ON u.user_id=i.user_id  LEFT JOIN tbl_evaluate e on w.user_id=e.user_evaluated_id");
                if (!EmptyUtil.isEmpty(programmerCareer)) {
                    WHERE("user_career= #{programmerCareer}");
                }
                if (!EmptyUtil.isEmpty(workPlace)) {
                    WHERE("region_city = #{workPlace}");
                }
                if (!EmptyUtil.isEmpty(workWay)) {
                    WHERE("work_way = #{workWay}");
                }
                WHERE("u.is_deleted = " + IsDeleteEnum.ZERO.status);
                GROUP_BY("u.user_id");

            }
        }}.toString();
    }


    public String deleteAll() {
        return null;
    }

    public String getProgrammers() {
        return new SQL() {{
            SELECT("any_value ( u.user_id ) userId,any_value ( u.nick_name ) nickName,any_value ( i.image_url ) imageUrl,any_value ( u.user_company ) programmerCompany,any_value ( u.user_career ) programmerCareer,any_value (e.evaluated_desc) evaluatedDesc,any_value (TRUNCATE ( sum( attitude_score + punctuality_score + skill_score )/ count( 1 )/ 3, 1 )) starRating");
            FROM("tbl_user u LEFT JOIN tbl_user_image i ON u.user_id = i.user_id LEFT JOIN tbl_evaluate e ON u.user_id = e.user_evaluated_id");
            GROUP_BY("u.user_id");
            ORDER_BY("starRating DESC");
            LIMIT(5);
        }}.toString();

    }

    public String getRecommend() {
        return new SQL() {{
            SELECT("u.user_id AS userId,u.nick_name AS nickName,i.image_url AS imageUrl,u.user_career AS userCareer,u.user_company AS userCompany,w.salary AS salary");
            FROM("tbl_user u JOIN tbl_user_image i ON u.user_id = i.user_id JOIN tbl_work w ON w.user_id=u.user_id");
            WHERE("u.user_career = #{userCareer} AND u.user_id !=#{userId} AND u.is_deleted=0");
        }}.toString();

    }


}
